package com.nstechs.commerce.transaction.productitem;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ConnectionUtils {

	private Connection conn = null;
	private static String CHECK_FOREIGN_KEY= "SET FOREIGN_KEY_CHECKS=0";
	private static Logger logger = LoggerFactory.getLogger("SEVERE");
	
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
	}

	private String url = "jdbc:mysql://127.0.0.1:3306/test";

	private String user = "root";

	private String password = "root";

	public ConnectionUtils(String user, String password, String url) {
		super();
		this.user = user;
		this.password = password;
		this.url = url;
	}

	public Connection getConnection() {
		if (conn == null) {
			try {
				conn = DriverManager.getConnection(url, user, password);
			} catch (SQLException e) {
				logger.error(e.getMessage());
			}
		}
		return conn;
	}

	public ResultSet executeQuery(String sql, Object... params) {
		ResultSet rs =null;
		try {
			getConnection();
			PreparedStatement pst = conn.prepareStatement(sql);
			setParams(pst, params);
			rs = pst.executeQuery();
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
		return rs;
	}
	
	public int excuteQueryAndGetKey(String sql, Object... params) {
		int count = 0;
		int key = 0;
		try {
			getConnection();
			PreparedStatement pst = conn.prepareStatement(sql);
			setParams(pst, params);
			count = pst.executeUpdate(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			if(count > 0){ 
				ResultSet rs = pst.getGeneratedKeys();
				if (rs.next()) {
					key= rs.getInt(1);
				}
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
		return key;
	}
	
	public void execute(String sql, Object... params) {
		try {
			getConnection();
			PreparedStatement pst = conn.prepareStatement(sql);
			setParams(pst, params);
			pst.execute();
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
	}

	public void executeBatch(String sql, List<Object[]> params) {
		try {
			getConnection();
			conn.prepareStatement(CHECK_FOREIGN_KEY).execute();
			PreparedStatement pst = conn.prepareStatement(sql);
			int count = 0;
			for (Object[] obj : params) {
				setParams(pst, obj);
				pst.addBatch();
				count ++;
				if(count % 100 == 0) {
					pst.executeBatch();
				}
			}
			pst.executeBatch();
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
	}
	
	public void closeConnection() {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
	}

	private void setParams (PreparedStatement pst, Object... params) {
		int i = 0;
		try {
			for (Object param : params) {
				i++;
				if (param == null) {
					pst.setNull(i, Types.CHAR);
					
				} else {
					pst.setObject(i, param);
				}
			}
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
		
	}
}
